In this step, 4 Dimension tables will be created from the
Fact Table to create a Snowflake Data model.
Step 6.1 – Create the dimension tables for Race and Weapon.
1. Select the column. In this case, the Shooter Race column.
2. Right click on the header and select add as new query.
3. On the File tab, click on To Table from the Convert Menu.
4. No modification needs to be done to the popping window.
Click Ok.
5. Right click on the columns header and select Remove
Duplicates.
6. Just like in step 3.1, add an index column “From 1”.
7. Rename the index column as RaceID
8. Rename the Column1 as Shooters_Race
9. Rename the table as DimRace.
10. Move RaceID to the beginning
11. Repeat the same steps to create DimWeapon.
Step 6.2 – Merge the queries.
1. In the FactTable, go to Home tab.
2. In the Combine menu, click on Merge Queries.
3. On the Merge window, select the matching tables to create
the merged table. In this case, ShootersRace column from the Fact Table.
4. In the drop-down menu, select DimRace.
5. Select the column Shooters_Race.
6. Select “Left Outer (all from first, matching from second)”
as the Join Kind.
7. Once you get the green checkmark at the bottom of the window, click OK. A new created column named DimRace will appear at the end of the Fact Table.
Step 6.3 – Delete unnecessary columns.
1. Go to the newly created column DimRace and click on the far-right
icon on the header.
2. Uncheck all the other columns and only select the column
with IDs. In this case, RaceID and click Ok.
3. Go ahead and delete from the Fact Table the column
ShootersRace since is no longer needed.
4. Apply steps 6.2 and 6.3 to the WeaponUsed column.
DimCity and DimState
To create these dimensions, we need columns that do not
exist directly in the Fact Table. These columns will be created from the column
Location from the Fact Table. After that, similar steps to steps 6.1, 6.2 and
6.3 will be applied to the created columns to create the dimension tables.
Step 6.4 – Create the City column.
1. Select the column Location from the Fact Table.
2. Go to the Add Column tab.
3. Click on Extract located in the From Text menu.
4. Select Text Before Delimiter.
5. Type in a comma as the delimiter.
6. Click Ok.
7. Find the new created column at the end of the table and
rename it to City.
Step 6.5 – Create the State column.
1. Select the column Location from the Fact Table.
2. Go to the Add Column tab.
3. Click on Extract located in the From Text menu.
4. Select Text After Delimiter.
5. Type in a comma as the delimiter.
6. Click Ok.
7. Find the new created column at the end.
8. Select the column and right click on the header.
9. Go to the Transform option and select Trim to eliminate the blank
space between the comma and the State’s name.
10. Rename the column to State.
Step 6.6 – Create the DimState
1. Now that there is a State column, to create DimState, just repeat
the same steps taken to create DimRace and DimWeapon.
Step 6.7 – Create the DimCity
1. Form the Queries menu on the vertical left side of the
screen, right click on FactTable.
2. Select Duplicate
3. Rename the table to DimCity.
4. Move to the end the necessary columns for this table (City,
State, StateID, Location, Latitude and Longitude).
5. Select all the 6 necessary columns by clicking on their
header while holding down the Ctrl key.
6. Right click on the header of any of these columns and select
“Remove Other Columns”.
7. Just like Step 6.1, remove duplicates and create an Index
column.
8. After that, repeat steps 6.2 and 6.3 to create the DimCity
All these tables should like this:
Step 6.8 – Creating the relationships between the dimension
tables and the Fact Table.
1. Drag the ID columns from the dimension
tables to the Fact Table.
Step 6.9 – Hiding unnecessary columns.
1. Hover to mouse over the right side of the column name that needs to be hidden.
2. If done correctly, an icon like this 
will appear. Click on it.
These are the 2 tables with hidden columns at this point.
After all these steps, the Data Model should look like this:
This project will analyze only shootings from 1982 to 2019. The last thing to do before jumping to the Dashboard is to filter 2020 from the year column. Later, if needed, it can be always brought back.
Step 6.10 – Replace values in the ShootersAge column an change it to Whole Number format
1. Right click on the ShootersAge column.
Select Replace Values
In value to find type "-"
Leave blank the filed Replace With and click Ok.
Change the column format from Text to Whole number.
